package com.fiap.gestaoPassagens.voo;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.commons.lang3.StringUtils;

import com.fiap.gestaoPassagens.dao.ConnectionFactory;


public class VooDao{

	public VooBean create(VooBean vooBean) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		ResultSet rs;
		int i=0;

		try {
			sql.append(" INSERT INTO VOO ");
			sql.append(" (CODIGO_VOO, ");
			sql.append("  ORIGEM_VOO, ");
			sql.append("  DESTINO_VOO, ");
			sql.append("  DT_PREVISTA_DECOLAGEM, ");
			sql.append("  DT_PREVISTA_POUSO) ");
			sql.append(" VALUES ( ?,?,?,?,?)  ");
			
			pstmt = con.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
			
			pstmt.setString(++i, vooBean.getCodigoVoo());
			pstmt.setString(++i, vooBean.getOrigemVoo());
			pstmt.setString(++i, vooBean.getDestinoVo());
			pstmt.setTimestamp(++i, new Timestamp(vooBean.getDataPrevistaDecolagem().getTime()));
			pstmt.setTimestamp(++i, new Timestamp(vooBean.getDataPrevistaPouso().getTime()));
			
			pstmt.executeUpdate();
			
			//Retorna o ID gerado.
			rs=pstmt.getGeneratedKeys();
			if(rs.next()){
				vooBean.setId(rs.getLong(1));
			}

		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			pstmt.close();
			con.close();
		}

		return vooBean;
	}
	
	public VooBean update(VooBean vooBean) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();

		int i=0;
		try {
			sql.append(" UPDATE VOO ");
			sql.append(" SET ");
			sql.append(" CODIGO_VOO  = ?, ");
			sql.append(" ORIGEM_VOO  = ?, ");
			sql.append(" DESTINO_VOO = ?, ");
			sql.append(" DT_PREVISTA_DECOLAGEM = ?, ");
			sql.append(" DT_PREVISTA_POUSO = ? ");
			sql.append(" WHERE ID = ? ");
			
			pstmt = con.prepareStatement(sql.toString());
			
			pstmt.setString(++i, vooBean.getCodigoVoo());
			pstmt.setString(++i, vooBean.getOrigemVoo());
			pstmt.setString(++i, vooBean.getDestinoVo());
			pstmt.setTimestamp(++i, new Timestamp(vooBean.getDataPrevistaDecolagem().getTime()));
			pstmt.setTimestamp(++i, new Timestamp(vooBean.getDataPrevistaPouso().getTime()));
			pstmt.setLong(++i, vooBean.getId());
			
			pstmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			pstmt.close();
			con.close();
		}

		return vooBean;
	}
	
	public void delete(VooBean vooBean) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();

		int i=0;
		try {
			sql.append(" DELETE FROM VOO ");
			sql.append(" WHERE ID = ? ");
			
			pstmt = con.prepareStatement(sql.toString());
			
			pstmt.setLong(++i, vooBean.getId());
			
			pstmt.executeUpdate();

		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			pstmt.close();
			con.close();
		}
	}
	
	public List<VooBean> list(VooBean vooBeanFiltros) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		ResultSet rs = null;
		int i=0;
		List<VooBean> lista = new ArrayList<VooBean>();

		try {
			sql.append(" SELECT * FROM  VOO ");
			sql.append(" WHERE 1=1 ");
			if(vooBeanFiltros.getId()>0){
				sql.append(" AND ID                   = ? ");
			}
			
			if(StringUtils.trimToNull(vooBeanFiltros.getCodigoVoo())!=null){
				sql.append(" AND CODIGO_VOO           = ? ");
			}
			
			if(StringUtils.trimToNull(vooBeanFiltros.getOrigemVoo())!=null){
				sql.append(" AND ORIGEM_VOO  		  = ? ");
			}
			
			if(StringUtils.trimToNull(vooBeanFiltros.getDestinoVo())!=null){
				sql.append(" AND DESTINO_VOO 		  = ? ");
			}
			
			if(vooBeanFiltros.getDataPrevistaDecolagem()!=null){
				sql.append(" AND DATE(DT_PREVISTA_DECOLAGEM)= DATE(?) ");
			}
			
			if(vooBeanFiltros.getDataPrevistaPouso()!=null){
				sql.append(" AND DATE(DT_PREVISTA_POUSO)    = DATE(?) ");
			}	
			
			sql.append(" ORDER BY ID ");
			
			pstmt = con.prepareStatement(sql.toString());
			
			if(vooBeanFiltros.getId()>0){
				pstmt.setLong(++i, vooBeanFiltros.getId());
			}
			
			if(StringUtils.trimToNull(vooBeanFiltros.getCodigoVoo())!=null){
				pstmt.setString(++i, vooBeanFiltros.getCodigoVoo());
			}
			
			if(StringUtils.trimToNull(vooBeanFiltros.getOrigemVoo())!=null){
				pstmt.setString(++i, vooBeanFiltros.getOrigemVoo());
			}
			
			if(StringUtils.trimToNull(vooBeanFiltros.getDestinoVo())!=null){
				pstmt.setString(++i, vooBeanFiltros.getDestinoVo());
			}
			
			if(vooBeanFiltros.getDataPrevistaDecolagem()!=null){
				pstmt.setTimestamp(++i, new Timestamp(vooBeanFiltros.getDataPrevistaDecolagem().getTime()));
			}
			
			if(vooBeanFiltros.getDataPrevistaPouso()!=null){
				pstmt.setTimestamp(++i, new Timestamp(vooBeanFiltros.getDataPrevistaPouso().getTime()));
			}
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				lista.add(this.mountBean(rs));
			}
			

		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			pstmt.close();
			con.close();
			rs.close();
		}
		return lista;
	}
	

	
	public VooBean mountBean(ResultSet rs) throws SQLException{
		VooBean v = new VooBean();
		v.setId(rs.getLong("ID"));
		v.setCodigoVoo(rs.getString("CODIGO_VOO"));
		v.setDataPrevistaDecolagem(new Date(rs.getTimestamp("DT_PREVISTA_DECOLAGEM").getTime()));//(new java.util.Date(rs.getTime("DT_PREVISTA_DECOLAGEM").getTime()));
		v.setDataPrevistaPouso(new Date(rs.getTimestamp("DT_PREVISTA_POUSO").getTime()));
		v.setDestinoVo(rs.getString("DESTINO_VOO"));
		v.setOrigemVoo(rs.getString("ORIGEM_VOO"));
		
		return v;
	}
}
